package dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jdbc.jdbc;
import pojo.Time;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TimeDaoImp implements TimeDao {
    /**
     * 增加课时表数据
     * @return
     */
    @Override
    public int insert(Time time) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql增加语句
        String sql="insert into schooltreasure.time(classTime,remarks)values('"+time.getClassTime()+"','"+time.getRemarks()+"')";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;

    }

    /**
     * 删除课时表数据
     * @param time
     * @return
     */
    @Override
    public int del_Update(Time time) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="update schooltreasure.time set invalid='"+time.getInvalid()+"' where id='"+time.getId()+"'";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }

    /**
     * 修改课时表数据
     * @param time
     * @return
     */
    @Override
    public int update(Time time) throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="update schooltreasure.time set classTime='"+time.getClassTime()+"',remarks='"+time.getRemarks()+"' where id='"+time.getId()+"'";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        int res=preparedStatement.executeUpdate();//执行sql语句，无论什么语句都能执行
        if(res==1){
            return 1;
        }
        jdbc.guanbi(null,preparedStatement,connection);
        return 0;
    }

    /**
     * 查看课时表数据
     * @return time
     */
    @Override
    public JSONArray ChaTime() throws SQLException {
        //建立链接
        jdbc jdbc=new jdbc();
        Connection connection=jdbc.lianjie();
        //sql删除语句
        String sql="SELECT * FROM schooltreasure.time;";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet res=preparedStatement.executeQuery();//执行sql语句，无论什么语句都能执行

        JSONArray jsonArray=new JSONArray();
        while (res.next()){
            JSONObject jsonObject=new JSONObject();
            if(res.getInt("invalid")!=1){
                jsonObject.put("id",res.getInt("id"));
                jsonObject.put("classTime",res.getString("classTime"));
                jsonObject.put("remarks",res.getString("remarks"));
                jsonObject.put("invalid",res.getInt("invalid"));
                jsonArray.add(jsonObject);
            }

        }
        jdbc.guanbi(res,preparedStatement,connection);
        return jsonArray;
    }
}
